package br.com.arndroid.coupman.model;

import br.com.arndroid.coupman.R;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class CouponOpenHelper extends SQLiteOpenHelper {

	// Help function for determining if database is opened or not in memory:
	private static String dataBaseName(boolean isInMemory) {
		return isInMemory ? null : "coupon_manager_db"; 
	}
	
	// Cols constants
	// Coupon:
	public static final String COUPON_TABLE_NAME = "coupon";
	public static final int COUPON_TABLE_ID_COL_INDEX = 0;
	public static final String COUPON_TABLE_ID_COL_NAME = "_id";
	public static final int COUPON_TABLE_SUMMARY_COL_INDEX = 1;
	public static final String COUPON_TABLE_SUMMARY_COL_NAME = "summary";
	public static final int COUPON_TABLE_NOTES_COL_INDEX = 2;
	public static final String COUPON_TABLE_NOTES_COL_NAME = "notes";
	public static final int COUPON_TABLE_NORMAL_VALUE_COL_INDEX = 3;
	public static final String COUPON_TABLE_NORMAL_VALUE_COL_NAME = "normal_value";
	public static final int COUPON_TABLE_PROMO_VALUE_COL_INDEX = 4;
	public static final String COUPON_TABLE_PROMO_VALUE_COL_NAME = "promo_value";
	public static final int COUPON_TABLE_EXPIRATION_DT_COL_INDEX = 5;
	public static final String COUPON_TABLE_EXPIRATION_DT_COL_NAME = "expiration_dt";
	public static final int COUPON_TABLE_RESERVATION_REQUIRED_COL_INDEX = 6;
	public static final String COUPON_TABLE_RESERVATION_REQUIRED_COL_NAME = "reservation_required";
	public static final int COUPON_TABLE_SCHEDULE_DT_COL_INDEX = 7;
	public static final String COUPON_TABLE_SCHEDULE_DT_COL_NAME = "schedule_dt";
	public static final int COUPON_TABLE_LOCAL_COL_INDEX = 8;
	public static final String COUPON_TABLE_LOCAL_COL_NAME = "local";
	public static final int COUPON_TABLE_URI_MEDIA_COL_INDEX = 9;
	public static final String COUPON_TABLE_URI_MEDIA_COL_NAME = "uri_media";
	public static final int COUPON_TABLE_FK_CATEGORY_COL_INDEX = 10;
	public static final String COUPON_TABLE_FK_CATEGORY_COL_NAME = "fk_category";
	public static final int COUPON_TABLE_FK_SITE_COL_INDEX = 11;
	public static final String COUPON_TABLE_FK_SITE_COL_NAME = "fk_site";
	public static final int COUPON_TABLE_STATE_COL_INDEX = 12;
	public static final String COUPON_TABLE_STATE_COL_NAME = "state";
	
	// Category:
	public static final String CATEGORY_TABLE_NAME = "category";
	public static final String CATEGORY_TABLE_ID_COL_NAME = "_id";
	public static final int CATEGORY_TABLE_ID_COL_INDEX = 0;
	public static final String CATEGORY_TABLE_NAME_COL_NAME = "name";
	public static final int CATEGORY_TABLE_NAME_COL_INDEX = 1;
	public static final String CATEGORY_TABLE_ICON_URI_COL_NAME = "icon_uri";
	public static final int CATEGORY_TABLE_ICON_URI_COL_INDEX = 2;

	// Site:
	public static final String SITE_TABLE_NAME = "site";
	public static final int SITE_TABLE_ID_COL_INDEX = 0;
	public static final String SITE_TABLE_ID_COL_NAME = "_id";
	public static final int SITE_TABLE_NAME_COL_INDEX = 1;
	public static final String SITE_TABLE_NAME_COL_NAME = "name";
	public static final int SITE_TABLE_URL_URI_COL_INDEX = 2;
	public static final String SITE_TABLE_URL_URI_COL_NAME = "url_uri";
	
	// Current db version:
	private static final int CURRENT_DB_VERSION = 1;
	
	// SQL for tables creation *** SQL updates must increment db version and requires onUpgrade atualization ***

	// Coupon:
	private static final String COUPON_CREATE_TABLE_SCRIPT = 
		"CREATE TABLE " + COUPON_TABLE_NAME + " (" +
			COUPON_TABLE_ID_COL_NAME + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
			COUPON_TABLE_SUMMARY_COL_NAME + " TEXT NOT NULL COLLATE NOCASE, " +
			COUPON_TABLE_NOTES_COL_NAME + " TEXT," +
			COUPON_TABLE_NORMAL_VALUE_COL_NAME + " REAL," +
			COUPON_TABLE_PROMO_VALUE_COL_NAME + " REAL," +
			COUPON_TABLE_EXPIRATION_DT_COL_NAME + " TEXT," +
			COUPON_TABLE_RESERVATION_REQUIRED_COL_NAME + " INTEGER," +
			COUPON_TABLE_SCHEDULE_DT_COL_NAME + " TEXT," +
			COUPON_TABLE_LOCAL_COL_NAME + " TEXT," +
			COUPON_TABLE_URI_MEDIA_COL_NAME + " TEXT," +
			COUPON_TABLE_FK_CATEGORY_COL_NAME + " INTEGER REFERENCES category (_id)," +
			COUPON_TABLE_FK_SITE_COL_NAME + " INTEGER REFERENCES site (_id)," +
			COUPON_TABLE_STATE_COL_NAME + " INTEGER NOT NULL" +			
		");";	
	
	// Site:
	private static final String SITE_CREATE_TABLE_SCRIPT = 
		"CREATE TABLE " + SITE_TABLE_NAME + " (" +
			SITE_TABLE_ID_COL_NAME + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
			SITE_TABLE_NAME_COL_NAME + " TEXT NOT NULL COLLATE NOCASE, " +
			SITE_TABLE_URL_URI_COL_NAME +
		");";
	private static final String SITE_CREATE_IDX_SCRIPT =
		"CREATE UNIQUE INDEX site_table_name_col_idx " +
			"ON " + SITE_TABLE_NAME + " (" + SITE_TABLE_NAME_COL_NAME + ");";
	private static final String SITE_POPULATE_TABLE_SCRIPT = 
		"INSERT INTO " + SITE_TABLE_NAME + "(" + SITE_TABLE_NAME_COL_NAME + ", " + SITE_TABLE_URL_URI_COL_NAME + ")" +
			"VALUES ('%1$s', '%2$s')";

	// Category:
	private static final String	CATEGORY_CREATE_TABLE_SCRIPT = 
		"CREATE TABLE " + CATEGORY_TABLE_NAME + " (" +
			CATEGORY_TABLE_ID_COL_NAME + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
			CATEGORY_TABLE_NAME_COL_NAME + " TEXT NOT NULL COLLATE NOCASE," +
			CATEGORY_TABLE_ICON_URI_COL_NAME + " TEXT NOT NULL" +
		");";
	private static final String CATEGORY_CREATE_IDX_SCRIPT =
		"CREATE UNIQUE INDEX category_table_name_col_idx " +
			"ON " + CATEGORY_TABLE_NAME + " (" + CATEGORY_TABLE_NAME_COL_NAME + ");";
	
	// DATA MEMBERS
	private Context internalCtx;

	public CouponOpenHelper(Context ctx, boolean isInMemory) {
		super(ctx, dataBaseName(isInMemory), null, CURRENT_DB_VERSION);
		internalCtx = ctx;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		String[] siteNames, siteUrls;
		
		// Create tables:
		db.beginTransaction();
		try {
			// Load data resources:
			siteNames = internalCtx.getResources().getStringArray(R.array.data_standard_site_names);
			siteUrls = internalCtx.getResources().getStringArray(R.array.data_standard_site_urls);
			// Arrays length must be equals or there are a error in definition for a paticular language
			if(siteNames.length != siteUrls.length)
				throw new IllegalArgumentException("Different siteNames/siteUrls.lenght in CouponOpenHelper.onCreate(SQLiteDatabase)");

			// Execution (order is important):
			db.execSQL(SITE_CREATE_TABLE_SCRIPT);
			db.execSQL(SITE_CREATE_IDX_SCRIPT);
			for(int i = 0; i < siteNames.length; i++)
				db.execSQL(String.format(SITE_POPULATE_TABLE_SCRIPT, siteNames[i], siteUrls[i]));
			db.execSQL(CATEGORY_CREATE_TABLE_SCRIPT);
			db.execSQL(CATEGORY_CREATE_IDX_SCRIPT);
			db.execSQL(COUPON_CREATE_TABLE_SCRIPT);			
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		} 
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// This version not admit any upgrade.
		throw new SQLiteException("Not prepared for database migration between versions " + oldVersion + " and " + newVersion);
	}

}
